import sqlite3
import os


if __name__ == '__main__':
    conn = sqlite3.connect(':memory:')
    c = conn.cursor()

    # 创建Table
    c.execute(
        '''
        CREATE TABLE Company(
            id INT PRIMARY KEY NOT NULL,
            name TEXT NOT NULL,
            address TEXT,
            age INT NOT NULL
        );
        '''
    )
 
    # conn.close()

    # Insert record to table
    c.execute(
        "INSERT INTO Company(id, name, address, age) VALUES(1, 'Tom', 'America', 20);"
    )
    c.execute(
        "INSERT INTO Company(id, name, address, age) VALUES(2, 'Allen', 'China', 23);"
    )
    c.execute(
        "INSERT INTO Company(id, name, address, age) VALUES(3, 'Marry', 'U.K', 18);"
    )

    # 查找、检索
    cursor = c.execute(
        "SELECT * FROM Company;"
    )

    for row in cursor:
        print(row)
    
    cursor = c.execute(
        "SELECT name FROM Company;"
    )
    for row in cursor:
        print(row)
    
    cursor = c.execute(
        "SELECT * FROM Company ORDER BY age DESC;"
    )
    for row in cursor:
        print(row)

    conn.commit()

